/*
 * Copyright (C) 2006 Davy Vanherbergen dvanherbergen@users.sourceforge.net
 * 
 * This program is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General
 * Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option)
 * any later version.
 * 
 * This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied
 * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
 * details.
 * 
 * You should have received a copy of the GNU Lesser General Public License along with this library; if not, write to
 * the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
 */
package net.sourceforge.sqlexplorer.dataset;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;

import net.sourceforge.sqlexplorer.ExplorerException;
import net.sourceforge.sqlexplorer.IConstants;
import net.sourceforge.sqlexplorer.Messages;
import net.sourceforge.sqlexplorer.dbproduct.SQLConnection;
import net.sourceforge.sqlexplorer.dbproduct.Session;
import net.sourceforge.sqlexplorer.plugin.SQLExplorerPlugin;

/**
 * Generic DataSet to hold values for TableViewer.
 * 
 * This class has been changed to remove dependencies on a fixed list of data types; this is to allow database-specific
 * data types. Since every row is represented as Objects (typically instances of String, Integer, Double, etc), it is
 * only a requirement that the cells implement the Comparable interface so that sorting works correctly. The textual
 * representation is obtained by calling toString() on the object.
 * 
 * Any code which used to use the TYPE_XXXX constants defined here should now use instanceof if knowledge of the
 * implementing type is required; however, be aware that non-standard types (i.e. types not defined in java.lang) may be
 * present.
 * 
 * @author Davy Vanherbergen
 * @modified John Spackman
 */
public class DataSet {

    public static class Column {

        private String caption;

        private boolean rightJustify;

        public Column(String caption, boolean rightJustify) {
            super();
            this.caption = caption;
            this.rightJustify = rightJustify;
        }

        public String getCaption() {
            return caption;
        }

        public boolean isRightJustify() {
            return rightJustify;
        }

        public Format getFormat() {
            return null;
        }
    }

    public static class FormattedColumn extends Column {

        private Format format;

        public FormattedColumn(String caption, boolean rightJustify, Format format) {
            super(caption, rightJustify);
            this.format = format;
        }

        @Override
        public Format getFormat() {
            return format;
        }
    }

    // Caption for the results tabs
    private String caption;

    private Column[] columns;

    private DataSetRow[] _rows;

    private DataSetTableSorter _sorter;

    // Whether dates are formatted (from preferences)
    private Boolean formatDates;

    /**
     * Create a new dataSet based on an existing ResultSet.
     * 
     * @param resultSet ResultSet with values [mandatory]
     * @param relevantIndeces int[] of all columns to add to the dataSet, use null if all columns should be included.
     * 
     * @throws Exception if the dataset could not be created
     */
    public DataSet(ResultSet resultSet, int[] relevantIndeces, int maxRows) throws SQLException {
        initialize(null, resultSet, relevantIndeces, maxRows);
    }

    /**
     * Create a new dataSet based on an existing ResultSet.
     * 
     * @param resultSet ResultSet with values [mandatory]
     * @param relevantIndeces int[] of all columns to add to the dataSet, use null if all columns should be included.
     * 
     * @throws Exception if the dataset could not be created
     */
    public DataSet(ResultSet resultSet, int[] relevantIndeces) throws SQLException {
        this(resultSet, relevantIndeces, 0);
    }

    /**
     * Create a new dataSet based on an existing ResultSet.
     * 
     * @param resultSet ResultSet with values [mandatory]
     * @param relevantIndeces int[] of all columns to add to the dataSet, use null if all columns should be included.
     * 
     * @throws Exception if the dataset could not be created
     */
    public DataSet(String caption, ResultSet resultSet, int[] relevantIndeces, int maxRows) throws SQLException {
        this.caption = caption;
        initialize(null, resultSet, relevantIndeces, maxRows);
    }

    /**
     * Create a new dataSet based on an existing ResultSet.
     * 
     * @param resultSet ResultSet with values [mandatory]
     * @param relevantIndeces int[] of all columns to add to the dataSet, use null if all columns should be included.
     * 
     * @throws Exception if the dataset could not be created
     */
    public DataSet(String caption, ResultSet resultSet, int[] relevantIndeces) throws SQLException {
        this(caption, resultSet, relevantIndeces, 0);
    }

    /**
     * Create new dataset based on sql query.
     * 
     * @param columnLabels string[] of columnLabels, use null if the column name can be used as label
     * @param sql query string
     * @param relevantIndeces int[] of all columns to add to the dataSet, use null if all columns should be included.
     * @param connection An open SQLConnection [mandatory]
     * @throws Exception if dataSet could not be created
     */
    public DataSet(String[] columnLabels, String sql, int[] relevantIndeces, Session session) throws SQLException,
            ExplorerException {
        SQLConnection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = session.grabConnection();
            statement = connection.createStatement();
            statement.execute(sql);
            resultSet = statement.getResultSet();
            initialize(columnLabels, resultSet, relevantIndeces, 0);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    SQLExplorerPlugin.error(Messages.getString("DataSet.errorCloseRs"), e);
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    SQLExplorerPlugin.error(Messages.getString("DataSet.errorCloseStmt"), e);
                }
            }
            if (connection != null) {
                session.releaseConnection(connection);
            }
        }
    }

    /**
     * Create new dataset based on String[][].
     * 
     * @param columnLabels string[] of columnLabels [mandatory]
     * @param data string[][] with values for dataset [mandatory]
     * @throws Exception if dataSet could not be created
     */
    public DataSet(String[] columnLabels, Comparable[][] data) {
        this(null, columnLabels, data);
    }

    /**
     * Create new dataset based on String[][].
     * 
     * @param caption
     * @param columnLabels string[] of columnLabels [mandatory]
     * @param data string[][] with values for dataset [mandatory]
     * @throws Exception if dataSet could not be created
     */
    public DataSet(String caption, String[] columnLabels, Comparable[][] data) {
        this.caption = caption;
        columns = convertColumnLabels(columnLabels);

        _rows = new DataSetRow[data.length];

        for (int i = 0; i < data.length; i++) {
            _rows[i] = new DataSetRow(this, data[i]);
        }
    }

    /**
     * Initialize dataSet based on an existing ResultSet.
     * 
     * @param columnLabels String[] of column labels [mandatory]
     * @param resultSet ResultSet with values [mandatory]
     * @param relevantIndeces int[] of all columns to add to the dataSet, use null if all columns should be included.
     * @throws Exception if the dataset could not be created
     */
    private void initialize(String[] columnLabels, ResultSet resultSet, int[] relevantIndeces, int maxRows) throws SQLException {

        ResultSetMetaData metadata = resultSet.getMetaData();

        int[] ri = relevantIndeces;

        // create default column indexes
        if (ri == null || ri.length == 0) {
            ri = new int[metadata.getColumnCount()];
            for (int i = 1; i <= metadata.getColumnCount(); i++) {
                ri[i - 1] = i;
            }
        }

        // create column labels
        if (columnLabels != null && columnLabels.length != 0) {
            columns = convertColumnLabels(columnLabels);
        } else {
            columns = new Column[ri.length];
            for (int i = 0; i < ri.length; i++) {
                int columnIndex = ri[i];
                columns[i] = createColumn(metadata, columnIndex);
            }
        }

        loadRows(resultSet, ri, maxRows);
    }

    /**
     * Called to create a Column object from the given metadata; this is broken out into a separate method so that
     * database-specific implementations can override it
     * 
     * @param metadata
     * @param columnIndex
     * @return
     * @throws SQLException
     */
    protected Column createColumn(ResultSetMetaData metadata, int columnIndex) throws SQLException {
        int type = metadata.getColumnType(columnIndex);

        // Numeric - figure out a display format
        if (type == Types.DECIMAL || type == Types.NUMERIC || type == Types.DOUBLE || type == Types.FLOAT || type == Types.REAL) {
            int precision = metadata.getPrecision(columnIndex);
            int scale = metadata.getScale(columnIndex);
            if (precision < 1 || scale > precision) {
                return new FormattedColumn(metadata.getColumnName(columnIndex), true, null);// new
                                                                                            // DecimalFormat("#.#"));
            }

            /*
             * NOTE: Scale can be negative (although possibly limited to Oracle), but we cope with this by specifing #
             * after the decimal place precision-1 times; eg a precision of 10 will return #.#########
             */
            StringBuffer sb = new StringBuffer(precision + 2);
            for (int j = 0; j < precision; j++) {
                if (scale < 0 || j < precision - scale - 1) {
                    sb.append('#');
                } else {
                    sb.append('0');
                }
            }

            if (scale > 0) {
                sb.insert(precision - scale, '.');
            } else if (scale < 0) {
                sb.insert(1, '.');
            }

            return new FormattedColumn(metadata.getColumnName(columnIndex), true, new DecimalFormat(sb.toString()));
        }

        if (type == Types.DATE || type == Types.TIMESTAMP || type == Types.TIME) {
            return new FormattedColumn(metadata.getColumnName(columnIndex), false, getDateFormat(type));
        }

        return new Column(metadata.getColumnName(columnIndex), false);
    }

    /**
     * Creates an array of Column descriptors from an array of strings
     * 
     * @param columnLabels
     * @return
     */
    private Column[] convertColumnLabels(String[] columnLabels) {
        Column[] result = new Column[columnLabels.length];
        for (int i = 0; i < columnLabels.length; i++) {
            result[i] = new Column(columnLabels[i], false);
        }
        return result;
    }

    /**
     * Get the column index for a given column name
     * 
     * @param name
     * @return index of column whose name matches or 0 if none found
     */
    public int getColumnIndex(String name) {
        for (int i = 0; i < columns.length; i++) {
            if (columns[i].getCaption().equalsIgnoreCase(name)) {
                return i;
            }
        }
        return 0;
    }

    /**
     * @return String[] with all column labels
     */
    public Column[] getColumns() {
        return columns;
    }

    /**
     * Obtain number of rows.
     * 
     * @return Number of rows.
     */
    public int getRowCount() {
        return _rows.length;
    }

    /**
     * @return all rows in this dataset
     */
    public DataSetRow[] getRows() {
        return _rows;
    }

    /**
     * Get a single row in this dataset.
     * 
     * @param index Index of row.
     * @return Row.
     * @throws IndexOutOfBoundsException if row at index isn't present.
     */
    public DataSetRow getRow(int index) {
        if (index < 0 || index >= _rows.length) {
            throw new IndexOutOfBoundsException(Messages.getString("DataSet.errorIndexOutOfRange") + index);
        }
        return _rows[index];
    }

    /**
     * Called to load rows from the specified result set; the default implementation simply uses standard JDBC data
     * types to inten to be overridden.
     * 
     * @param resultSet ResultSet to load from
     * @param relevantIndeces int[] of all columns to add to the dataSet, use null if all columns should be included.
     */
    protected void loadRows(ResultSet resultSet, int[] relevantIndeces, int maxRows) throws SQLException {
        ResultSetMetaData metadata = resultSet.getMetaData();

        // create rows
        ArrayList rows = new ArrayList(maxRows > 0 ? maxRows : 100);
        int rowCount = 0;
        while (resultSet.next() && (maxRows == 0 || rowCount < maxRows)) {
            DataSetRow row = new DataSetRow(this);
            for (int i = 0; i < columns.length; i++) {
                int columnIndex = relevantIndeces != null ? relevantIndeces[i] : i;
                Comparable obj = loadCellValue(columnIndex, metadata.getColumnType(columnIndex), resultSet);
                if (resultSet.wasNull()) {
                    row.setValue(i, null);
                } else {
                    row.setValue(i, obj);
                }
            }
            rows.add(row);
            rowCount++;
        }
        _rows = (DataSetRow[]) rows.toArray(new DataSetRow[] {});
    }

    /**
     * Loads a given column from the current row in a ResultSet; can be overridden to provide database-specific
     * implementation
     * 
     * @param columnIndex
     * @param dataType
     * @param resultSet
     * @return
     * @throws SQLException
     */
    protected Comparable loadCellValue(int columnIndex, int dataType, ResultSet resultSet) throws SQLException {
        switch (dataType) {
        case Types.INTEGER:
        case Types.SMALLINT:
        case Types.TINYINT:
            return new Long(resultSet.getInt(columnIndex));

        case Types.BIGINT:
            return new Long(resultSet.getLong(columnIndex));

        case Types.DECIMAL:
        case Types.NUMERIC:
        case Types.DOUBLE:
        case Types.FLOAT:
        case Types.REAL:
            int precision = resultSet.getMetaData().getPrecision(columnIndex);
            if (precision > 16 || precision < 1) {
                return resultSet.getBigDecimal(columnIndex);
            }
            return new Double(resultSet.getDouble(columnIndex));

            // MOD qiongli 2014-4-29 TDQ-8769,void to lose "hh:mm:ss.SSS" for Date type.so revert to use default
            // "getTimestamp(...)".for vertica Date type,it will be catched and use "getDate(...)"
        case Types.DATE:
        case Types.TIMESTAMP:
            Comparable dateTime = null;
            try {
                dateTime = resultSet.getTimestamp(columnIndex);
            } catch (SQLException exc) {
                if (dataType == Types.DATE) {
                    dateTime = resultSet.getDate(columnIndex);
                } else {
                    SQLExplorerPlugin.error(exc.getMessage());
                }
            }
            return dateTime;

        case Types.TIME:
            return resultSet.getTime(columnIndex);

        default:
            // MOD yyi 2012-05-25 TDQ-5460 : Fix getString of JConnector(sybase).
            try {
                return resultSet.getString(columnIndex);
            } catch (SQLException e) {
                // MOD yyi 2012-04-17 TDQ-5176 : Change get string to get bytes for mess decode.
                return null == resultSet.getBytes(columnIndex) ? null : new String(resultSet.getBytes(columnIndex));
            }
        }
    }

    /**
     * Resort the data using the given column and sortdirection.
     * 
     * @param columnIndex primary sort column index
     * @param sortDirection SWT.UP | SWT.DOWN
     */
    public void sort(int columnIndex, int sortDirection) {
        if (_sorter == null) {
            _sorter = new DataSetTableSorter(this);
        }
        _sorter.setTopPriority(columnIndex, sortDirection);

        Arrays.sort(_rows, _sorter);
    }

    private DateFormat getDateFormat(int type) {
        SimpleDateFormat dateFormat = null;
        if (formatDates == null) {
            formatDates = SQLExplorerPlugin.getDefault().getPluginPreferences().getBoolean(IConstants.DATASETRESULT_FORMAT_DATES);
        }
        if (!formatDates) {
            return null;
        }

        // MOD qiongli 2014-4-29,format Time data type as "hh:mm:ss.SSS";format Date and Timestamp as "yyyy-mm-dd HH:mm:ss.SSS".
        if (type == Types.TIME) {
            dateFormat = new SimpleDateFormat(SQLExplorerPlugin.getDefault().getPluginPreferences()
                    .getString(IConstants.DATASETRESULT_TIME_FORMAT));
        } else {
            dateFormat = new SimpleDateFormat(SQLExplorerPlugin.getDefault().getPluginPreferences()
                    .getString(IConstants.DATASETRESULT_DATE_FORMAT));
        }

        return dateFormat;
    }

    public String getCaption() {
        return caption;
    }
}
